Certains aficionados de PostGreSQL tente de comparer PostGreSQL à Oracle, affirmant que PostGreSQL fonctionne presque pareil…
Alors, puisque c’est le cas, renchérissent leurs économes, ne payons plus de licences et migrons que diable !
Pourtant de nombreux spécialistes des bases de données ayant une importante expérience dans l’utilisation de divers SGBDR et ayant dû utiliser PostGreSQL en remplacement d’Oracle ou SQL Server s’en sont mordu les doigts…
Voici quelques uns des écueils qui peuvent vous attendre le jour ou vous passerez d’une base commerciale comme IBM DB2 UDB, Oracle ou SQL Server à PostGreSQL.

1 – Aucune gestion des espaces de stockage.

Dans les SGBDR comme Oracle, SQL Server ou IBM DB2, il est possible de gérer les espaces de stockage de manière très précise, et cela afin de savoir précisément ou se trouve une table ou un index, en sus de pouvoir régler la taille et le taux de croissance des espaces de stockage.
Le principe est généralement le suivant. A une base de données est associé un ou plusieurs espaces de stockage (« storage » dans la littérature anglo saxonne) que l’on appelle « tablespace » dans Oracle et « filegroup » dans SQL Server. Ces espaces de stockage constitue la destination du stockage des données des tables. A la création d’une table ou d’un index, il suffit de préciser le « storage » que l’on souhaite affecté à l’objet.
Chaque storage doit comporter au moins un fichier, mais peut en compter plusieurs.
Pour chaque fichier créé on peut généralement préciser la taille minimale, maximale et le pas de croissance.
En fixant une taille minimale, le fichier est directement créé de la taille précisé (ce n’est pas une pré réservation d’espace comme on le croit trop souvent) et l’algorithme utilisé permet d’aller scruter les disques à la recherche du meilleur emplacement possible pour les données. Ce meilleur emplacement sur un disque neuf est constitué par les cylindres extérieur de tous les plateaux !

Le but de ce système est double :

• permettre de ventiler les entrées/sorties (IO), à condition que chaque fichier soit créé sur un disque physique indépendant (au revoir les VM et attention aux sans avec des LUNs taillées dans la masse, toutes choses qui constituent une plaie pour les DBE).
• Mais aussi diminuer les temps d’accès disque. On voit bien qu’en utilisant les cylindres externes des disques on fait d’une pierre deux coups : on peut lire des données « simultanément » sur différents plateaux à la fois et le temps d’accès tend vers celui de la vitesse de rotation des disques.

De plus, les SGBDR comme Oracle, SQL Server ou IBM DB2, sont capable de regrouper les pages à écrire par contiguïté géographique, afin de minimiser le trajet de la tête de lecture. C’est loin d’être négligeable et Michael Stonebraker qui a mise en évidence ce problème [1] et réalisé quelques uns des algorithmes pour ce faire le sait bien… Il est aussi à l’origine de postGreQSL ! Encore faut-il que les espaces de stockage aient été créée en un seul bloc pour éviter toute fragmentation !

Enfin, Oracle et SQL Server permettent de placer les espaces de stockage que l’on souhaite en READ ONLY, ce qui permet d’éviter tout verrouillage.

A lire, en complément sur le sujet : Question sur les fichiers et le stockage

Or PostGreSQL ne dispose d’aucune gestion des espaces de stockage, autre que celle de déclarer un ou plusieurs répertoire en tant que « storage » pour une base.
La notion de tablespace PG ne fait qu’indiquer un répertoire.
Voici la syntaxe PG (v9.1.1):
CREATE TABLESPACE tablespace_name [ OWNER user_name ] LOCATION 'directory'
Ce que vous ne pouvez pas faire avec PostGreSQL :
1) vous ne pouvez pas indiquer une taille minimale de l’espace de stockage
2) vous ne pouvez pas indiquer une taille maximale de l’espace de stockage
3) vous ne pouvez pas indiquer un pas de croissance de l’espace de stockage
4) vous ne pouvez pas ajouter un autre répertoire a un tablespace déja créé
5) vous ne pouvez pas placer un espace de stockage en READ ONLY pour des tables statiques afin d’éviter le verrouillage
Toutes ces possibilités existent sur Oracle, SQL Server, IBM Db2, Sybase ASE…

2 – Une gestion des transactions « curieuse ».

Une fonction SQL (il n’existe pas de procédure stockée dans PostGreSQL) peut encapsuler n’importe quel code SQL du DML (INSERT, SELECT, UPDATE, DELETE) et semble agir comme une procédure stockée, à une exception près : il est impossible de gérer une transaction à l’intérieur du code d’une fonction postGreSL. En fait PostGreSQL considère qu’une fonction = une transaction !
Ceci pose de nombreux problèmes…
Le fait qu’une fonction soit en elle même une transaction est un choix plus que discutable. En effet que se passe t-il si vous avez lancé une transaction qui lance une procédure ? (transactions imbriquées)
Le cas des transactions imbriquées est toujours complexe, mais sans méthode de résolution, il est difficile de savoir ce qui se passe.
En sus ce n’est pas parce que il y a une erreur que l’on doit considérer automatiquement que la transaction doit être annulé. C’est en principe l’auteur du code et lui seul qui doit décider de la règle de gestion et non le SGBDR !
Considérant par exemple, une gestion de stock, on peut essayer de déstocker du matériel et partir en erreur dans une table car on dépasse le seuil. Mais on peut alors décider en cas d’erreur de lancer une commande. Ce que visiblement PG ne sais pas faire au sein d’une même fonction…

On présente souvent le langage PLpgSQL comme l’équivalent du PL/SQL d’Oracle… Il n’est en évidemment rien. Seul quelques éléments de syntaxe sont commun…

Quand à la récriture d’une base de données avec de nombreuses transactions effectuées dans de nombreuses procédures, c’est un véritables cauchemar à transposer en PostGreSQL !

Un exemple nous est fournit avec la demande suivante émanent du forum PostGreSQL : « Pouvoir lancer deux fonctions qui utilisent la même table ». Ce serait si simple avec une gestion de transactions à l’intérieur de la procédure !!!

3 РUn partitionnement plus que l̩ger

Comme le dit Mladen Gogala « Postgres partitioning is not very robust » ce que confirme différents internautes (voir ce message).
Dans cet article « Partitionner une table. Comparaison PostGreSQL / MS SQL Server » je mentionne l’extrême complexité du partitionnement et les risques énormes de se tromper.
Les développeurs de PostGreSQL conscient du problème ont réagit. A lire « Table Partitionning » mais hélas ce ne sont que des paroles ! Une nouvelle version du partitionnement devait voir le jour avec la version 9.0. Nous en sommes à la 9.1 et toujours rien !

4 РPas de tag (hint) pour forcer les plans de requ̻te

L’un des principaux griefs des Oracliens sur PostGreSQL est l’absence de hint (ou tag de requête) permettant de forcer le plan. Ce peut être par exemple d’indiquer qu’il faut toujours prendre tel index et jamais la table ou que l’algorithme de la jointure doit être celui-ci et non un autre.
L’utilisation des tags de requête ne doit pas être entrepris à la légère car il fige un plan qui devrait être réévalué à chaque modification de la distribution des données comme du volume. Mais il y a des cas ou la science de l’optimiseur est pris en défaut et d’autre ou l’on souhaite contraindre explicitement, par exemple pour éviter un verrou de table.
Or la philosophe particulièrement croustillante des développeurs de PG est que c’est très mal de mettre des tags dans les requêtes : « Why PostgreSQL Doesn’t Have Query Hints »
Dans un monde parfait, cela serait absolument génial… Mais malheureusement il y a la réalité, et là force est de constater que l’optimisation des requêtes par PostGreSQL est loin d’être… optimale !

Voici un exemple calamiteux ou PostGreSQL choisit de lire une table constituée de 50 pages plutôt que de lire un index de 2 pages (testé en version 9.1) :

CREATE TABLE T (NID INT NOT NULL, DATA VARCHAR(8000)); 

 

INSERT INTO T SELECT 1, REPEAT('@', 8000); 

 

INSERT INTO T SELECT MAX(NID), REPEAT('@', 8000) FROM T; 

--> répétée 50 fois 

 

CREATE INDEX X ON T (NID); 

 

SELECT NID FROM T;

Le plan de requête de PostGreSQL est le suivant :

Seq Scan on t (cost=0.00..11.40 rows=140 width=4)

On voit que PostGreSQL balaye lamentablement toute la table alors qu’il devrait utiliser l’index qui ne contient que la colonne visée par le SELECT !

Dans un SGBDR commercial qui commettrait une telle étourderie, on rajouterais par exemple un tag du style :

SELECT NID FROM T WITH(INDEX(X));

Les seuls paramétrages de PostGreSQL le sont au niveau du serveur ou de la session, ce qui ne permet vraiment pas de corriger les erreur de l’optimiseur pour certaines requêtes…

Mais il existe une alternative… Utiliser une version payante de PostGreSQL, appelée EnterpriseDB qui, curieusement intègre les tags !.
Ce qui contredit les détracteurs du « y’a pas besoin de tag de requête dans SQL Server, puisque son optimiseur est parfait ! »… Mais alors pourquoi et comment EnterpriseDB arrive-il à vendre leur solution (4 495 $ par an, service d’assistance compris) ?

5 Рun optimiseur tr̬s limit̩

Nous venons de voir une erreur de l’optimiseur très grossière… Ce n’est hélas pas la seule. Dans l’article sur le partitionnement nous avons montré des plans de requêtes particulièrement épouvantable ôtant tout intérêt au partitionnement
Il n’est qu’a consulter la mailing list consacré au sujet des performances de PostGreSQL pour constater les difficultés de certains pour obtenir un plan de requête efficace.
Une des anomalies les plus fréquente est celle concernant le calcul des statistiques qui permettent à l’optimiseur de choisir le bon plan de requête. Il semble qu’il faille régulièrement changer le nombre d’histogramme de l’échantillonnage que PG effectue. Quel est le bon réglage ? À priori il semble dépendre de la dispersion des données et du nombre de lignes… Bref, une partie du travail de l’optimiseur doit être fait au quotidien par le DBA… Pas très pratique !

6 – Une indexation à la traine

En matière d’index, PostGreSQL montre certaines lacunes. Par exemple il n’y a toujours pas d’index couvrants (clause INCLUDE), ni d’index XML et la méthode d’indexation de PostGIS avec les index GIST est loin d’être d’une grande efficacité. Pour un comparaison, lire « Tout sur l’index »

Il n’existe pas non plus de vues matérialisées comme ce qu’offre Oracle ou de vues indexées à la SQL Server. C’est pourtant d’une redoutable efficacité dès que l’on a des calculs d’agrégats portant sur de forts volumes de données.

En sus il n’y a pas moyen de savoir quel sont les index manquant comme on le trouve par exemple dans MS SQL Server avec les vues systèmes sys.dm_db_missing_index_details et autres.
Bien qu’il soit possible de savoir comment sont utilisés les index (vue pg_stat_sys_indexes) les informations ne montrent pas le nombre de mise à jour effectuées sur l’index qu’il faut aller récupérer par approximation au niveau table (pg_stat_all_tables).

Liste des index manquants (MS SQL Server)

Avec PostGreSQL, il faut donc deviner quel sont les index à poser et ceux à supprimer, et comme les outils de monitoring sont assez pauvres, la partie est loin d’être gagnée…

7 РPas de parall̩lisme des requ̻tes

Lors d’un congrès PG Days organisé par Dalibo, Bull montrait comment les caisses d’allocations familiales avaient migré un ancien système propriétaire de stockage des données en le remplaçant par PostGreSQL. Discutant avec l’un des responsable du projet, ce dernier me confiait que pour lui le principal manque de PostGreSQL résidait dans le fait de l’absence de parallélisme pour traiter une même requête.
Oracle, SQL Server et DB2 savent faire cela depuis de nombreuses années…
Les conséquences de cet absence de parallélisme peuvent être très problématiques sur de grands volumes :

• il augmente considérablement le temps de réponse d’une requête
• la table étant verrouillée plus longtemps la concurrence d’accès diminue

Tout cela ne plaide pas en faveur de l’exploitation de grandes bases de données avec PostGreSQL.

8 – Une administration couteuse

Lorsque l’on compare les outils administration des grands SGBDR par rapport à l’offre PostGreSQL le DBA se sent immédiatement frustré !
Son entreprise ne le sait pas encore mais il va lui falloir passer beaucoup plus de temps, car les outils, de monitoring comme ceux administration sont encore embryonnaires. Nous en avons déjà parlé au niveau des index ou tout doit se faire à la main (recherches des index manquants ou inutiles). Un seul exemple nous montrera l’étendu de l’écart. SQL Server dispose d’un outil de reniflage de l’activité du serveur (le profiler SQL) qui permet de lister par exemple toutes les requêtes et procédures, leurs temps CPU ou intrinsèque, le nombre de lectures ou d’écriture, etc et ce de manière ou débusquer les optimisation a entreprendre. Associé à RML par exemple il permet de faire des tests comparatif en reproduisant exactement (y compris la concurrence des différentes sessions) la charge d’une base ou d’un serveur sur une autre machine configurée différemment.
En comparaison, PostGreSQL ne propose que des outils frustes, simplistes et rarement « friendely user » : pgfouine / trsung. Il faut aller en chercher quelques uns ailleurs, payant bien sûr. Et trouver la documentation pour savoir s’en servir correctement est une gageure car il n’existe aucune méthodologie…

Ne parlons pas du merveilleux (?) VACUUM, bonne à tout faire de PostGreSQL que la plupart des DBA habitués à des outils précis et peu intrusifs exècre… Il bloque et lock à mort !

9 – pas de pooling des connexions

La plupart des SGBDR comme Oracle ou SQL Server intègre un pooling de connexions. Pas PostgreSQL. Il faut rajouter un module nommé PG Pool pour ce faire. Le pooling permet d’éviter que trop de connexion écroulent le système, en faisant alterner les connexions et les threads d’exécution entre les différentes demandes.

10 – Pas de vision d’un plan en cours d’utilisation

L’absence de « shared pool » (cache de procédure partagé) rend impossible de lire le plan d’une requête qui vient d’être exécutée, contrairement à ce que l’on peut faire avec Oracle ou SQL Server

11 – Un processus de sauvegarde peu performant

PostGreSQL n’a pas la possibilité de paralléliser sa sauvegarde sur plusieurs fichiers simultanément, ce qui ne permet pas de diviser par n (n étant le nombre de support de la sauvegarde) le temps de la sauvegarde.
De même PostGreSQL ne permet pas de réaliser des sauvegardes différentielles, c’est à dire des sauvegardes qui ne prennent en compte que les données ayant changées depuis la dernière sauvegarde.
Ce sont des choses indispensables lorsque l’on commence à attaquer des bases de grandes volumétries.

12 РPas de compression ni de cryptage des donn̩es

PostGreSQL ne permet pas de compresser les données à aucun moment et n’intègre aucun mécanisme de cryptage. En comparaison SQL Server intégre le cryptage depuis la version 2005 et la compression au niveau page ou ligne depuis la version 2008.

13 РPas de r̩plication des donn̩es *

Mise à Part les contributions externes Slony et Londist (peu performant car basés sur des triggers), PostGreSQL ne dispose d’aucun mécanisme intégré de réplication des données, contrairement à ce qui se fait par exemple chez SQL Server ou l’on dispose de 4 modes de réplications en fonction des besoins, en sus de Service Broker qui peut être utilisé dans ce sens : réplication transactionnelle, réplication par cliché (snapshot), réplication de fusion et réplication point à point.

14 РPostGreSQL reste un bon SGBDR r̩ellement libre

Après avoir brossé ce tableau noir, dire de PostGreSQL qu’il reste un bon SGBDR semble utopique… Mais c’est pourtant vrai !
En effet, aucun autre SGBDR dans le libre n’arrive à la hauteur de PostGreSQL en terme de fonctionnalités, de fiabilité et de performances, lorsqu’on le maintient dans certaines limites. Il m’arrive de le préconiser en alternative aux SGBDR des principaux éditeurs lorsque ces derniers veulent ne pas payer de licence. Je leur fait connaître les limites de l’outil et leur indique que l’administration risque d’être un peu plus couteuse, notamment par rapport à SQL Server.

Il convient donc de cantonner PostGreSQL a ce qu’il sait bien faire. C’est à dire comme tout bon outil, lui faire faire ce pourquoi il est fait.
En particulier, les limitations absolues que je considère actuellement à l’utilisation de PostGreSQL sont les suivantes :

• Une centaine d’utilisateurs concurrents (il peut y en avoir bien plus de connecté, mais évitions qu’ils lancent touts au même moment leur requête !)
• Des bases limitées à quelques centaines de Go (pour ma part je ne conseille pas d’aller au delà de 250 Go toutes bases confondues sur un même serveur). Par exemple en l’absence d’un mécanisme de sauvegarde performant le temps de la sauvegarde peu devenir un réel handicap.
• Des requêtes limitées à quelques tables en jointures ou sous requêtes.
• Une complexité transactionnelle limitée (découper au maximum par les fonctions PG)
• Pas de décisionnel. PostGreSQL ne propose pas de technique de stockage spécifique pour gérer des bases de données OLAP comme le non stockage du NULL, la compression des données, le précalcul d’agrégat, le stockage des résultats de requête en cache…

En matière de requête portant sur un fort volume de données, je me suis amusé à montré ce que pouvait impliquer en terme de performance les manques fonctionnels des SGBDR comme PostGreSQL et MySQL. Dans cet article « Agrégation d’intervalles en SQL : en pratique » on constate que PostGreSQL est à terme 10 fois moins rapide que SQL Server…

15 – En conclusion

Vous comprendrez que présenter PostGreSQL comme une alternative crédible à Oracle ou même SQL Server sur des bases à forte volumétrie, important trafic ou grande concurrence et est un phantasme de nerd mais est loin d’être une réalité !
Soyons modeste pour ne pas avoir de couteuses déconvenues et évitons les dogmes.
Je sais qu’en rédigeant un tel article je vais m’attirer les foudres de nombreux internautes. Je m’y attend et reste serein. J’ai des arguments et une expérience acquise depuis plus de 20 ans par mon travail avec de nombreux SGBDR…
Mais je sais hélas que, chez certains, la passion du libre l’emporte souvent contre la raison du technique !

NOTE :

* Une réplication de données consiste à prendre certaines données de certaines tables et placer ces informations dans une autre base. Certains confondent réplication de données et duplication d’une base. Un mécanisme comme la « réplication streaming » n’est pas à proprement parler une réplication, mais la duplication d’une base, c’est à dire un mécanisme de haute disponibilité. C’est l’équivalent par exemple du mirroring asynchrone dans SQL Server.

RÉFÉRENCES :

Livre cité

[1] A lire dans « Readings in Database Systems » de Joseph M. Hellerstein et Michael Stonebraker

NON, PostGreSQL n’est pas un clone d’Oracle !

Database preferences and product selection methodolgy
« Oracle vs. Postgres (PostgreSQL) »

Migration

Migration de Oracle vers PostGreSQL
Guide de migration vers PostGreSQL

Manques et problèmes de PostGreSQL

Top 10 Missing PostgreSQL Features
Top Missing PostgreSQL Features
PostgreSQL Gotchas (attention, cela date un peu)
Une discussion animée sur l’absence des tags : Why PostgreSQL doesn’t have query hints

Modules externes compensatoires :
contribution pg_stat_statements pour surveiller les statistiques d’exécution de tous les ordres SQL exécutés par un serveur.
contribution pgcrypto, propose des fonctions de cryptographie
module pg_statsinfos (sté NTT OSS) rapports statistiques d’activité du serveur

Fonctionnement interne de PostgreSQL
Postgres Internals Presentations (Bruce Momjian)
A Tour of PostgreSQL Internals (Tom Lane)
Inside the PostgreSQL Shared Buffer Cache (Greg Smith)

Performances PostGreSQL vs SQL Server

Même chez Enterprise DB qui fournit un PostGreSQL Amélioré on est conscient des manques de postGreSQL qui font la différence. Question de crédibilité !
PostgreSQL Performance vs. Microsoft SQL Server
Un des rares benchmark sérieux : Open Source PostgreSQL Trails Oracle In Benchmark, But Not By Much

Une exemple de comparatif inepte :

Oracle vs PostgreSQL
A la lecture de cet édifiant tissus de mensonges et d’âneries on se demande encore pourquoi tant d’entreprise et de collectivités continuent d’utiliser Oracle…

Un exemple de comparatif technique très documenté :

PostgreSQL vs Oracle

Plans de requêtes et tags (hints en anglais)

Why the F&#% Doesn’t Postgres Have Hints?!?!
Le point de vu d’un contributeur PG

DISCUSSIONS…

Mon article ayant fait polémique, les internautes ont postés un peu partout. Voici quelques un des liens pour suivre les conversations animées en cours…
Forum PostGreSQL de developpez.net
Forum francophone de PostGreSQL.fr

Un exemple de la problématique de non existence des sauvegardes différentielles (liste pgsql-general@postgresql.org)
Are file system level differential/incremental backups possible?

****************************
POUR SUIVRE LA DISCUSSION…
****************************

N’hésitez pas à lire les commentaires et en postez vous même à l’entrée figurant dans ce lien….

-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *